# -*- coding: utf-8 -*-

import pymysql


class MysqlUtil:
    tableName = ""
    fieldName = " * "
    whereStr = " WHERE 1 "
    orderStr = ""
    limitStr = ""
    dataArray = []
    dataDic = {}
    cursor = None
    db = None
    lastSql = ""
    groupStr = ""

    def __init__(self, host, port, user, password, db):
        self.db, self.cursor = self.__getDBCusor(host, int(port), user, password, db)

    def resetDefaultData(self):
        self.tableName = ""
        self.fieldName = " * "
        self.whereStr = " WHERE 1 "
        self.orderStr = ""
        self.limitStr = ""
        self.groupStr = ""
        self.dataArray = []

    def __getDBCusor(self, host, port, user, password, db):
        db = pymysql.connect(host=host, port=port, user=user, password=password, database=db, autocommit=True,
                             cursorclass=pymysql.cursors.DictCursor)
        cursor = db.cursor()
        return db, cursor

    def table(self, tableName):
        self.tableName = tableName
        return self

    def field(self, field):
        if isinstance(field, str):
            self.fieldName = field
        if isinstance(field, list):
            self.fieldName = "`" + "`,`".join(field) + "`"
        return self

    def data(self, dataArray):
        if isinstance(dataArray, dict):
            self.dataDic = dataArray
        elif isinstance(dataArray, list):
            self.dataArray = dataArray
        return self

    def where(self, whereData):
        if isinstance(whereData, str):
            self.whereStr = " WHERE " + whereData
        if isinstance(whereData, dict):
            tmpWhereArray = []
            for key in whereData:
                value = str(whereData[key])
                value = value.replace("\\", "\\\\")
                value = value.replace("'", "\\'")
                tmpWhere = "`" + str(key) + "` = '" + value + "'"
                tmpWhereArray.append(tmpWhere)
            self.whereStr = " WHERE " + " AND ".join(tmpWhereArray)
        return self

    def order(self, orderStr):
        self.orderStr = " ORDER BY " + str(orderStr)
        return self

    def group(self, group):
        self.groupStr = " GROUP BY " + group

    def limit(self, limitStr):
        self.limitStr = " LIMIT " + str(limitStr)
        return self

    def insert(self, onDuplicateKey=""):
        data0 = self.dataArray[0]
        keyArray = data0.keys()
        keyString = "`,`".join(keyArray)
        valueStringArray = []
        for data in self.dataArray:
            dataValues = data.values()
            tmpDataValues = []
            for value in dataValues:
                value = str(value)
                value = value.replace("\\", "\\\\")
                value = value.replace("'", "\\'")
                tmpDataValues.append(value)
            valueString = "('" + "','".join(tmpDataValues) + "')"
            valueStringArray.append(valueString)
        sql = "INSERT INTO `" + self.tableName + "` (`" + keyString + "`) VALUES " + ",".join(valueStringArray)
        if onDuplicateKey != "":
            sql = sql + " ON DUPLICATE KEY UPDATE " + onDuplicateKey
        self.lastSql = sql
        self.cursor.execute(sql)
        self.resetDefaultData()

    def find(self):
        sql = "SELECT " + self.fieldName + " FROM " + self.tableName + self.whereStr + self.groupStr + self.orderStr + self.limitStr
        self.cursor.execute(sql)
        self.lastSql = sql
        results = self.cursor.fetchall()
        self.db.commit()
        self.resetDefaultData()
        return results

    def delete(self):
        sql = "DELETE FROM " + self.tableName + self.whereStr
        self.cursor.execute(sql)
        self.lastSql = sql
        self.db.commit()
        self.resetDefaultData()

    def save(self):
        setStrArray = []
        for key in self.dataDic:
            value = self.dataDic[key]
            value = str(value)
            value = value.replace("\\", "\\\\")
            value = value.replace("'", "\\'")
            setStr = "`" + str(key) + "` = '" + value + "'"
            setStrArray.append(setStr)
        if len(setStrArray) == 0:
            return
        setStrAll = ",".join(setStrArray)
        sql = "UPDATE " + self.tableName + " SET " + setStrAll + self.whereStr
        self.lastSql = sql
        self.cursor.execute(sql)
        self.db.commit()

    def execute(self, sql):
        self.cursor.execute(sql)
        self.lastSql = sql
        results = self.cursor.fetchall()
        self.db.commit()
        self.resetDefaultData()
        return results

    def query(self, sql):
        self.cursor.execute(sql)
        self.lastSql = sql
        results = self.cursor.fetchall()
        self.db.commit()
        self.resetDefaultData()
        return results

    def close(self):
        self.db.close()
